import pyodbc
import pandas as pd
import os

# Step 1: Define the SQL Server connection parameters
server = 'DESKTOP-IVSLP52'  # e.g., 'localhost' or 'your_ip_address'
database = 'AdventureWorksDW2022'  # Target database name

output_folder = r"C:\Users\hp\Desktop\CSV Files"  # Set a folder where CSV files will be saved

# Step 2: Connect to SQL Server using Windows Authentication (Trusted Connection)
conn = pyodbc.connect(f'DRIVER={{SQL Server}};SERVER={server};DATABASE={database};Trusted_Connection=yes;')
cursor = conn.cursor()

# Step 3: Get the list of tables in the AdventureWorks database
query = """
SELECT TABLE_SCHEMA, TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = ?
"""
cursor.execute(query, database)

tables = cursor.fetchall()

# Step 4: Define the list of tables you want to exclude
exclude_tables = [
    ("dbo", "DatabaseLog"),  # Example: ('dbo', 'Address')
    ("dbo", "AWBuildVersion")   # Add more tuples of (schema, table) as needed
]

# Step 4: Create output folder if not exists
if not os.path.exists(output_folder):
    os.makedirs(output_folder)

# Step 5: Loop through tables and export each to a CSV file
for table_schema, table_name in tables:
    if (table_schema, table_name) not in exclude_tables:
        # Formulate SQL query for each table
        sql_query = f"SELECT * FROM [{table_schema}].[{table_name}]"
        
        # Fetch the data into a pandas DataFrame
        df = pd.read_sql(sql_query, conn)
        
        # Define the file path for the CSV
        csv_file_path = os.path.join(output_folder, f"{table_schema}_{table_name}.csv")
        
        # Export the DataFrame to a CSV file
        df.to_csv(csv_file_path, index=False)
    
        print(f"Exported {table_name} to {csv_file_path}")
    else:
        print(f"Skipped table {table_name}")

Skipped table DatabaseLog

C:\Users\hp\AppData\Local\Temp\ipykernel_4660\1912201048.py:8: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  df = pd.read_sql(sql_query, conn)

Exported AdventureWorksDWBuildVersion to C:\Users\hp\Desktop\CSV Files\dbo_AdventureWorksDWBuildVersion.csv
Exported DimAccount to C:\Users\hp\Desktop\CSV Files\dbo_DimAccount.csv
Exported DimCurrency to C:\Users\hp\Desktop\CSV Files\dbo_DimCurrency.csv
Exported DimCustomer to C:\Users\hp\Desktop\CSV Files\dbo_DimCustomer.csv
Exported DimDate to C:\Users\hp\Desktop\CSV Files\dbo_DimDate.csv
Exported DimDepartmentGroup to C:\Users\hp\Desktop\CSV Files\dbo_DimDepartmentGroup.csv
Exported DimEmployee to C:\Users\hp\Desktop\CSV Files\dbo_DimEmployee.csv
Exported DimGeography to C:\Users\hp\Desktop\CSV Files\dbo_DimGeography.csv
Exported DimOrganization to C:\Users\hp\Desktop\CSV Files\dbo_DimOrganization.csv
Exported DimProduct to C:\Users\hp\Desktop\CSV Files\dbo_DimProduct.csv
Exported DimProductCategory to C:\Users\hp\Desktop\CSV Files\dbo_DimProductCategory.csv
Exported DimProductSubcategory to C:\Users\hp\Desktop\CSV Files\dbo_DimProductSubcategory.csv
Exported DimPromotion to C:\Users\hp\Desktop\CSV Files\dbo_DimPromotion.csv
Exported DimReseller to C:\Users\hp\Desktop\CSV Files\dbo_DimReseller.csv
Exported DimSalesReason to C:\Users\hp\Desktop\CSV Files\dbo_DimSalesReason.csv
Exported DimSalesTerritory to C:\Users\hp\Desktop\CSV Files\dbo_DimSalesTerritory.csv
Exported DimScenario to C:\Users\hp\Desktop\CSV Files\dbo_DimScenario.csv
Exported FactAdditionalInternationalProductDescription to C:\Users\hp\Desktop\CSV Files\dbo_FactAdditionalInternationalProductDescription.csv
Exported FactCallCenter to C:\Users\hp\Desktop\CSV Files\dbo_FactCallCenter.csv
Exported FactCurrencyRate to C:\Users\hp\Desktop\CSV Files\dbo_FactCurrencyRate.csv
Exported FactFinance to C:\Users\hp\Desktop\CSV Files\dbo_FactFinance.csv
Exported FactInternetSales to C:\Users\hp\Desktop\CSV Files\dbo_FactInternetSales.csv
Exported FactInternetSalesReason to C:\Users\hp\Desktop\CSV Files\dbo_FactInternetSalesReason.csv
Exported FactProductInventory to C:\Users\hp\Desktop\CSV Files\dbo_FactProductInventory.csv
Exported FactResellerSales to C:\Users\hp\Desktop\CSV Files\dbo_FactResellerSales.csv
Exported FactSalesQuota to C:\Users\hp\Desktop\CSV Files\dbo_FactSalesQuota.csv
Exported FactSurveyResponse to C:\Users\hp\Desktop\CSV Files\dbo_FactSurveyResponse.csv
Exported NewFactCurrencyRate to C:\Users\hp\Desktop\CSV Files\dbo_NewFactCurrencyRate.csv
Exported ProspectiveBuyer to C:\Users\hp\Desktop\CSV Files\dbo_ProspectiveBuyer.csv
Exported sysdiagrams to C:\Users\hp\Desktop\CSV Files\dbo_sysdiagrams.csv

# Step 6: Close the connection
conn.close()